﻿/****************************** Module Header ******************************\
 * Module Name:  GeneralDataTypeHelper.cs
 * Module Version:  1.0.0 (2013-7-11)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to operate database.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace UCL.Data
{
    /// <summary>
    /// 通用数据类型助手类[2013-7-11 by sutroon]
    /// </summary>
    public class GeneralDataTypeHelper
    {
        /// <summary>
        /// 获取通用数据类型
        /// 需要长度的类型：
        /// </summary>
        public string[] GeneralDbTypes
        {
            get
            {
                return new string[] { "Integer", "Short integer", "Long integer", "Byte", "Number", "Decimal", "Float", "Short float", "Long float", "Money", "Serial", "Boolean", "Characters", "Variable characters", "Long characters", "Long var characters", "Text", "Multibyte", "Vaiable mulibyte", "Date", "Time", "Datetime", "Timestamp", "Binary", "Variable binary", "Long binary", "Bitmap", "Image", "OLE", "Other", "Undefined" };
            }
        }

        /// <summary>
        /// 获取SQLServer数据类型
        /// <para>需要长度的类型:char | varchar | nchar | nvarchar | binary | varbinary</para>
        /// </summary>
        public string[] SQLServerDbTypes
        {
            get
            {
                return new string[] { "int", "smallint", "bigint", "tinyint", "numeric", "decimal", "float", "real", "double precision", "money", "numeric", "bit", "char", "varchar", "varchar", "varchar", "text", "nchar", "nvarchar", "datetime", "datetime", "datetime", "timestamp", "binary", "varbinary", "binary", "image", "image", "image", "undefined", "undefined" };
            }
        }

        /// <summary>
        /// 获取MySQL数据类型
        /// <para>需要长度的类型:char | varchar | national char | national varchar | blob | VBIN | long blob</para>
        /// </summary>
        public string[] MySQLDbTypes
        {
            get
            {
                return new string[] { "int", "smallint", "bigint", "tinyint", "numeric", "decimal", "float", "real", "double", "float", "int", "bool", "char", "varchar", "longtext", "longtext", "text", "national char", "national varchar", "date", "time", "datetime", "timestamp", "blob", "VBIN", "long blob", "long blob", "long blob", "long blob", "undefined", "undefined" };
            }
        }

        /// <summary>
        /// 获取SQLite数据类型
        /// </summary>
        public string[] SQLiteDbTypes
        {
            get
            {
                return new string[] { "INTEGER", "INTEGER", "INTEGER", "INTEGER", "NUMERIC", "NUMERIC", "REAL", "REAL", "REAL", "REAL", "INTEGER", "NUMERIC", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "NUMERIC", "NUMERIC", "NUMERIC", "NUMERIC", "BLOB", "BLOB", "BLOB", "BLOB", "BLOB", "BLOB", "undefined", "undefined" };
            }
        }

        /// <summary>
        /// 获取Access数据类型
        /// </summary>
        public string[] AccessDbTypes
        {
            get
            {
                return new string[] { "INTEGER", "SMALLINT", "LONG", "BYTE", "NUMERIC", "NUMBER", "FLOAT", "SINGLE", "DOUBLE", "CURRENCY", "COUNTER", "YESNO", "CHAR", "VARCHAR", "LONGCHAR", "LONGTEXT", "TEXT", "TEXT", "TEXT", "DATE", "TIME", "DATETIME", "TIMESTAMP", "BINARY", "VBIN", "BINARY", "GENERAL", "GENERAL", "OLEOBJECT", "UNDEF", "UNDEF" };
            }
        }

        /// <summary>
        /// 将具体数据库类型转为通用数据类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public string GetGeneralDbTypeName(string typeName, string providerName)
        {
            int i = -1;
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    i = SQLServerDbTypes.IndexOf(a => a.ToLower() == typeName.ToLower());
                    break;
                case "MySql.Data.MySqlClient":
                    i = MySQLDbTypes.IndexOf(a => a.ToLower() == typeName.ToLower());
                    break;
                case "System.Data.SQLite":
                    i = SQLiteDbTypes.IndexOf(a => a.ToLower() == typeName.ToLower());
                    break;
                case "System.Data.OleDb":
                    i = AccessDbTypes.IndexOf(a => a.ToLower() == typeName.ToLower());
                    break;
            }
            if (i > -1) typeName = GeneralDbTypes[i];
            return typeName;
        }

        /// <summary>
        /// 将通用数据类型转换为SQLSERVER数据库数据类型
        /// </summary>
        /// <param name="gTypeName"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public string GetProductDbTypeName(string gTypeName, string providerName)
        {
            int i = Array.IndexOf(GeneralDbTypes, gTypeName);
            string[] lenKeys = null;
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    if (i > -1) gTypeName = SQLServerDbTypes[i];
                    lenKeys = new string[] { "char", "varchar", "nchar", "nvarchar", "binary", "varbinary" };
                    break;
                case "MySql.Data.MySqlClient":
                    lenKeys = new string[] { "char", "varchar", "national char", "national varchar", "blob", "vbin", "long blob" };
                    if (i > -1) gTypeName = MySQLDbTypes[i];
                    break;
                case "System.Data.SQLite":
                    if (i > -1) gTypeName = SQLiteDbTypes[i];
                    break;
                case "System.Data.OleDb":
                    if (i > -1) gTypeName = AccessDbTypes[i];
                    break;
            }
            if (lenKeys != null && gTypeName.ToLower().IsIn(lenKeys)) gTypeName = gTypeName + "%";
            return gTypeName;
        }

        /// <summary>
        /// 获取数据库提供商名称与数据库连接提供程序的集合
        /// <para>Name|Provider</para>
        /// </summary>
        /// <returns></returns>
        public DataTable GetDBProviderSet(string firstName = "", string firstProvider = "")
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Provider");
            if (!string.IsNullOrEmpty(firstProvider)) dt.Rows.Add(firstName, firstProvider);
            dt.Rows.Add(DatabaseProduct.SQLServer.ToString(), "System.Data.SqlClient");
            dt.Rows.Add(DatabaseProduct.MySQL.ToString(), "MySql.Data.MySqlClient");
            dt.Rows.Add(DatabaseProduct.SQLite.ToString(), "System.Data.SQLite");
            dt.Rows.Add(DatabaseProduct.Access.ToString(), "System.Data.OleDb");
            return dt;
        }

        /// <summary>
        /// 获取数据库类型对象
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        /// <remarks>2013-9-25 by sutroon</remarks>
        public DatabaseProduct GetDatabaseProvider(string providerName)
        {
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    return DatabaseProduct.SQLServer;
                case "MySql.Data.MySqlClient":
                    return DatabaseProduct.MySQL;
                case "System.Data.SQLite":
                    return DatabaseProduct.SQLite;
                case "System.Data.OleDb":
                    return DatabaseProduct.Access;
            }
            return DatabaseProduct.Unknow;
        }

        /// <summary>
        /// 获取数据库类型名称的数组
        /// <para>2013-8-28 by sutroon</para>
        /// </summary>
        /// <returns></returns>
        public string[] GetDataBaseNames()
        {
            return Enum.GetNames(typeof(DatabaseProduct));
        }

        /// <summary>
        /// 获取指定数据库类型的数据类型列表
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public string[] GetDbTypes(string providerName)
        {
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    return SQLServerDbTypes;
                case "MySql.Data.MySqlClient":
                    return MySQLDbTypes;
                case "System.Data.SQLite":
                    return SQLiteDbTypes;
                case "System.Data.OleDb":
                    return AccessDbTypes;
                default:
                    return GeneralDbTypes;
            }
        }

        /// <summary>
        /// 获取指定数据库类型的需要定义字段长度数据类型列表
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public string[] GetDbTypesOfLengthly(string providerName)
        {
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    return new string[] { "char", "varchar", "nchar", "nvarchar", "binary", "varbinary" };
                case "MySql.Data.MySqlClient":
                    return new string[] { "char", "varchar", "national char", "national varchar", "blob", "vbin", "long blob" };
                default:
                    return null;
            }
        }
    }
}
